package Database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;

public class OracleConnection
{
	private static OracleConnection conn1 = null;
	private static OracleConnection conn2 = null;

	public static Connection connection1 = null;
	public static Connection connection2 = null;

	private static final String user1 = "system";
	private static final String pass1 = "parola";
	private static final String addr1 = "jdbc:oracle:thin:@192.168.92.128:1521:orcl";

	private static final String user2 = "system";
	private static final String pass2 = "parola";
	private static final String addr2 = "jdbc:oracle:thin:@192.168.92.129:1521:orcl";

	private OracleConnection(int connectionNumber)
	{
		// Register JDBC driver
		String driver = "oracle.jdbc.driver.OracleDriver";

		try
		{
			Class.forName(driver);
		} catch (ExceptionInInitializerError eiie)
		{
			System.out.println(eiie.getMessage());
		} catch (LinkageError le)
		{
			System.out.println(le.getMessage());
		} catch (ClassNotFoundException cnfe)
		{
			System.out.println(cnfe.getMessage());
		}

		if (connectionNumber == 1)
		{
			// Deschidere conexiune 1
			System.out.println("Connecting to database 1...");
			String jdbcUrl = addr1;
			String user = user1;
			String password = pass1;

			try
			{
				connection1 = DriverManager.getConnection(jdbcUrl, user,
						password);
			} catch (SQLException sqle)
			{
				System.out.println(sqle.getMessage());
			}
		} else if (connectionNumber == 2)
		{
			// Deschidere conexiune 2
			System.out.println("Connecting to database 2...");
			String jdbcUrl = addr2;
			String user = user2;
			String password = pass2;

			try
			{
				connection2 = DriverManager.getConnection(jdbcUrl, user,
						password);
			} catch (SQLException sqle)
			{
				System.out.println(sqle.getMessage());
				// sqle.printStackTrace();
			}
		}
	}

	public static OracleConnection getOracleConnection1()
	{
		if (conn1 == null)
		{
			// Opening connection for database 1
			conn1 = new OracleConnection(1);
		}

		return conn1;
	}

	public static OracleConnection getOracleConnection2()
	{
		if (conn2 == null)
		{
			// Opening connection for database 2
			conn2 = new OracleConnection(2);
		}

		return conn2;
	}

	public static SolutionForView selectResult(String autor, int selectionType)
	{
		SolutionForView sfv = new SolutionForView();

		if (selectionType == 1)
			selectResultFromDB1(autor, sfv, selectionType);
		else if (selectionType == 2)
			selectResultFromDB2(autor, sfv, selectionType);
		else if (selectionType == 3)
		{
			selectResultFromDB1(autor, sfv, selectionType);
			selectResultFromDB2(autor, sfv, selectionType);
		}

		return sfv;
	}

	// selectie din prima baza de date
	public static void selectResultFromDB1(String autor, SolutionForView sfv1,
			int selectionType)
	{
		String selectStatement = "SELECT a.nume numeAutor, b.nrcol numarColaborari  FROM autor a, (SELECT idaut, count(*) nrcol FROM autor_carte, (SELECT idautor FROM autor WHERE nume = ?) c WHERE idcar in (SELECT idcar FROM autor_carte WHERE idaut = c.idautor) AND idaut != c.idautor GROUP BY idaut) b WHERE a.idautor = b.idaut";
		try
		{
			PreparedStatement prepStmt = connection1
					.prepareStatement(selectStatement);
			prepStmt.setString(1, autor);

			ResultSet rs = prepStmt.executeQuery();

			System.out.println("Autorul " + autor
					+ "\na colaborat cu - numar de colaborari: ");
			while (rs.next())
			{
				// Retrieve by column name
				String numeAutor = rs.getString("numeAutor");
				int nrColaborari = rs.getInt("numarColaborari");

				sfv1.getColabsBooks().add(numeAutor);
				sfv1.getPcommBooks().add(nrColaborari);

				// daca s-au solicitat carti si articole
				if (selectionType == 3)
				{
					sfv1.getCombined().add(numeAutor);
					sfv1.getpCombined().add(nrColaborari);
				}
			}

			rs.close();
			prepStmt.close();
		} catch (SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
	}

	// selectie din a doua baza de date
	public static void selectResultFromDB2(String autor, SolutionForView sfv2,
			int selectionType)
	{
		String selectStatement = "SELECT a.nume numeAutor, b.nrcol numarColaborari  FROM autor a, (SELECT idaut, count(*) nrcol FROM autor_articol, (SELECT idautor FROM autor WHERE nume = ?) c WHERE idart in (SELECT idart FROM autor_articol WHERE idaut = c.idautor) AND idaut != c.idautor GROUP BY idaut) b WHERE a.idautor = b.idaut";
		try
		{
			PreparedStatement prepStmt = connection2
					.prepareStatement(selectStatement);
			prepStmt.setString(1, autor);

			ResultSet rs = prepStmt.executeQuery();

			System.out.println("Autorul " + autor
					+ "\na colaborat cu - numar de colaborari: ");
			while (rs.next())
			{
				// Retrieve by column name
				String numeAutor = rs.getString("numeAutor");
				int nrColaborari = rs.getInt("numarColaborari");

				// if (sfv2.colabsArticles.contains(rs))
				sfv2.getColabsArticles().add(numeAutor);
				sfv2.getPcommArticles().add(nrColaborari);

				// daca s-au solicitat si carti si articole, adun lucrarile
				// comune
				if (selectionType == 3)
				{
					int index = sfv2.getCombined().indexOf(numeAutor);
					if (index != -1)
					{
						int valoare = sfv2.getpCombined().get(index);
						sfv2.getpCombined().set(index, valoare + nrColaborari);
					} else
					{
						sfv2.getCombined().add(numeAutor);
						sfv2.getpCombined().add(nrColaborari);
					}
				}
			}

			rs.close();
			prepStmt.close();
		} catch (SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
	}
	
	public static void insertIntoDB(Paper paper)
	{
		if (paper instanceof Book)
			insertIntoDB1((Book) paper);
		else
			insertIntoDB2((Article) paper);
			
	}

	private static void insertIntoDB1(Book carte)
	{
		Connection conn = null;
		conn = connection1;
		// Step-2: identify the stored procedure
		String insertIntoDB1Procedure = "{ call insertIntoDB1(?, ?, ?, ?, ?) }";
		// Step-3: prepare the callable statement
		try
		{
			
			Iterator<Author> it = carte.authorsList.iterator();
			while (it.hasNext())
			{
				CallableStatement cs = conn.prepareCall(insertIntoDB1Procedure);
				// Step-4: set input parameters ...
				// first input argument
				
				Author aut = it.next();
				cs.setString(1, aut.nume);
				cs.setString(2, aut.contact);

				cs.setString(3, carte.titlu);
				cs.setString(4, carte.editura);
				cs.registerOutParameter(5, java.sql.Types.VARCHAR);

				cs.execute();
			}
/*
			CallableStatement cs = conn.prepareCall(insertIntoDB1Procedure);
			// Step-4: set input parameters ...
			// first input argument
			cs.setString(1, autor.nume);
			cs.setString(2, autor.contact);

		//	cs.setString(1, carte.authorsList.get(i));
			cs.setString(3, carte.titlu);
			cs.setString(4, carte.editura);
			cs.registerOutParameter(5, java.sql.Types.VARCHAR);

			cs.execute();
			System.out.println("Rezultat inserare " + cs.getString(5));
			*/
		} catch (SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
	}

	private static void insertIntoDB2(Article articol)
	{
		Connection conn = connection2;
		// Step-2: identify the stored procedure
		String insertIntoDB2Procedure = "{ call insertIntoDB2(?, ?, ?, ?, ?) }";
		// Step-3: prepare the callable statement
		try
		{
			/*
			CallableStatement cs = conn.prepareCall(insertIntoDB2Procedure);
			// Step-4: set input parameters ...
			// first input argument
			cs.setString(1, autor.nume);
			cs.setString(2, autor.contact);

			cs.setString(3, articol.titlu);
			cs.setString(4, articol.publicatie);
			cs.registerOutParameter(5, java.sql.Types.VARCHAR);

			cs.execute();
			System.out.println("Rezultat inserare " + cs.getString(5));*/
			
			Iterator<Author> it = articol.authorsList.iterator();
			while (it.hasNext())
			{
				CallableStatement cs = conn.prepareCall(insertIntoDB2Procedure);
				// Step-4: set input parameters ...
				// first input argument
				
				Author aut = it.next();
				cs.setString(1, aut.nume);
				cs.setString(2, aut.contact);

				cs.setString(3, articol.titlu);
				cs.setString(4, articol.publicatie);
				cs.registerOutParameter(5, java.sql.Types.VARCHAR);

				cs.execute();
			}
			
			
		} catch (SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
	}

	public static void closeConnections()
	{
		if (connection1 != null)
		{
			try
			{
				connection1.close();
			} catch (SQLException sqle)
			{
				System.out.println(sqle.getMessage());
			}
		}

		if (connection2 != null)
		{
			try
			{
				connection2.close();
			} catch (SQLException sqle)
			{
				System.out.println(sqle.getMessage());
			}
		}
	}
}
